CREATE DATABASE reform OWNER postgres;
GRANT ALL PRIVILEGES ON DATABASE reform to postgres;

\c reform
CREATE TABLE users(
    id INT NOT NULL,
    password VARCHAR(64) NOT NULL,
    salt VARCHAR(32) NOT NULL,
    device INT DEFAULT(0),-- 0:disabled  num(1-20): device assigned
    user_group INT DEFAULT(1),-- 0:administrator  1:normal
    register_time timestamp default now(),

  CONSTRAINT PK_id PRIMARY KEY (id)
);

CREATE TABLE history(
    user_id INT NOT NULL,
    t timestamp NOT NULL,
    generator_i INT NOT NULL,
    generator_u INT NOT NULL,
    payload_i INT NOT NULL,
    payload_u INT NOT NULL,
    pwm INT NOT NULL,

  CONSTRAINT PK_t PRIMARY KEY (t)
);


CREATE OR REPLACE FUNCTION history_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE user_id_text TEXT;
DECLARE insert_statement TEXT;
BEGIN
    SELECT New.user_id INTO user_id_text;
    insert_statement := 'INSERT INTO history_'
        || user_id_text
        ||' VALUES ($1.*)';
    EXECUTE insert_statement USING NEW;
    RETURN NULL;
    EXCEPTION
    WHEN UNDEFINED_TABLE
    THEN
        EXECUTE
            'CREATE TABLE IF NOT EXISTS history_'
            || user_id_text
            || '(CHECK (user_id = '''
            || user_id_text
            || ''')) INHERITS (history)';
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE history_%', user_id_text;
        EXECUTE insert_statement USING NEW;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
--挂载分区Trigger
CREATE TRIGGER insert_history_partition_trigger
BEFORE INSERT ON history
FOR EACH ROW EXECUTE PROCEDURE history_partition_trigger();
